﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Configuration;

namespace AlumniProject.Framework
{
    public class DBAccess
    {
        private readonly string _connectionString = WebConfigurationManager.ConnectionStrings["sqlserverconnectionstring"].ConnectionString;
        public SqlConnection GetConnection()
        {
            return new SqlConnection(_connectionString);
        }

        //Lay du lieu khong co tham so truyen vao
        public DataSet LayDuLieu(string procName)
        {
            SqlConnection con = GetConnection();
            DataSet data = new DataSet();
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.Connection = con;
            command.CommandText = procName;
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(data);
            return data;
        }

        //Load dữ liệu có tham số truyền vào (select from where)
        public DataSet LayDuLieu(string procName, string[] name, object[] value, int Nparameter)
        {
            try
            {
                SqlConnection con = GetConnection();
                SqlCommand command = new SqlCommand();
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = con;
                command.CommandText = procName;
                for (int i = 0; i < Nparameter; i++)
                {
                    command.Parameters.AddWithValue(name[i], value[i]);
                }
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                DataSet dt = new DataSet();
                adapter.Fill(dt);
                return dt;
            }
            catch (SqlException ex)
            {
                return null;
            }
        }

        public DataTable ExcuteQuery(String procName, CommandType type, params SqlParameter[] parameters)
        {
            var connection = GetConnection();

            var command = new SqlCommand(procName, connection) { CommandType = type };

            var table = new DataTable();

            try
            {
                if (parameters != null)
                    foreach (var sqlParameter in parameters)
                    {
                        command.Parameters.Add(sqlParameter);
                    }
                var adapter = new SqlDataAdapter(command);

                adapter.Fill(table);
            }
            catch (Exception exception)
            {
                table.Columns.Add("status", typeof(string));
                table.Columns.Add("message", typeof(string));

                table.Rows.Add("error", exception.Message);

            }

            return table;

        }

        public DataTable ExcuteNonQueryTable(String procName, CommandType type, params SqlParameter[] parameters)
        {
            var connection = GetConnection();

            var command = new SqlCommand(procName, connection) { CommandType = type };



            var table = new DataTable();

            try
            {
                connection.Open();
                var transaction = connection.BeginTransaction();
                if (parameters != null)
                    foreach (var sqlParameter in parameters)
                    {
                        command.Parameters.Add(sqlParameter);
                    }
                if (0 < command.ExecuteNonQuery())
                {
                    table.Columns.Add("status", typeof(string));
                    table.Columns.Add("message", typeof(string));

                    table.Rows.Add("success", "Success");

                    transaction.Commit();
                }
                else
                {
                    table.Columns.Add("status", typeof(string));
                    table.Columns.Add("message", typeof(string));

                    table.Rows.Add("error", "Failure, non row executed");
                }


            }
            catch (SqlException sqlException)
            {
                table.Columns.Add("status", typeof(string));
                table.Columns.Add("message", typeof(string));

                table.Rows.Add("error", sqlException.Message);
            }
            finally
            {
                connection.Close();
            }
            return table;
        }

        public Boolean ExcuteNonQuery(String procName, CommandType type, params SqlParameter[] parameters)
        {
            var connection = GetConnection();

            var command = new SqlCommand(procName, connection) { CommandType = type };

            connection.Open();
            var transaction = connection.BeginTransaction();


            var res = false;

            try
            {

                command.Transaction = transaction;
                if (parameters != null)
                    foreach (var sqlParameter in parameters)
                    {
                        command.Parameters.Add(sqlParameter);
                    }

                int resint = command.ExecuteNonQuery();

                if (0 < resint)
                {
                    res = true;

                    transaction.Commit();
                }


            }
            catch (Exception exception)
            {
                transaction.Rollback();
                res = false;
            }
            finally
            {
                connection.Close();
            }
            return res;
        }
    }
}